using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
   public class tbkhachhangDAL
    {
        private SqlConnection sqlCn = null;
        static public string error = "";
        public tbkhachhangDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            sqlCn = new SqlConnection(stringConnection);
            sqlCn.Open();
        }
       ~tbkhachhangDAL()
        {
            //this.sqlCn.Close();
            //this.sqlCn.Dispose();
        }

        public void Insert(tbkhachhangDTO kh)
        {
            SqlCommand cm = new SqlCommand("sp_khachhang_Insert", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

           
            //cm.Parameters.AddWithValue("@Makh", kh.Makh);
            cm.Parameters.AddWithValue("@tenkh",kh.Tenkh);
            cm.Parameters.AddWithValue("@diachi", kh.Diachi);
            cm.Parameters.AddWithValue("@dienthoai", kh.Dienthoai);
            cm.Parameters.AddWithValue("@email", kh.Email);
            cm.Parameters.AddWithValue("@matkhau", kh.Matkhau);
            cm.Parameters.AddWithValue("@diemtichluy", kh.Diemtichluy);
            
           
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return;
            }
        }

       public void Update(tbkhachhangDTO kh)
        {
            SqlCommand cm = new SqlCommand("sp_khachhang_Update", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@makh", kh.Makh);
            cm.Parameters.AddWithValue("@tenkh", kh.Tenkh);
            cm.Parameters.AddWithValue("@diachi", kh.Diachi);
            cm.Parameters.AddWithValue("@dienthoai", kh.Dienthoai);
            cm.Parameters.AddWithValue("@email", kh.Email);
            cm.Parameters.AddWithValue("@matkhau", kh.Matkhau);
            cm.Parameters.AddWithValue("@diemtichluy", kh.Diemtichluy);
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return;
            }
        }

        public void Delete(int Ma)
        {
            SqlCommand cm = new SqlCommand("sp_khachhang_Delete", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@Makh", Ma);

            cm.ExecuteNonQuery();
        }

        public tbkhachhangcollection GET(string Where, int PageIndex, int PageSize, string OrderBy,
                                       string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand("sp_khachhang_GET", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;


            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);

            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);


            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);

            tbkhachhangcollection khcollection = new tbkhachhangcollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                tbkhachhangDTO kh = new tbkhachhangDTO();
                kh.Makh = int.Parse(ds.Tables[0].Rows[i]["makh"].ToString());
                kh.Tenkh= ds.Tables[0].Rows[i]["tenkh"].ToString();
                kh.Diachi = ds.Tables[0].Rows[i]["diachi"].ToString();
                kh.Dienthoai= ds.Tables[0].Rows[i]["dienthoai"].ToString();
                kh.Email = ds.Tables[0].Rows[i]["email"].ToString();
                kh.Matkhau = ds.Tables[0].Rows[i]["matkhau"].ToString();

                kh.Diemtichluy = int.Parse(ds.Tables[0].Rows[i]["diemtichluy"].ToString());


                khcollection.Add(kh);

            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());

            return khcollection;
        }
       public tbkhachhangDTO GetByEmail(string email)
       {
           string cautruyvan = "select * from khachhang where email='" + email + "'";
           SqlDataAdapter da = new SqlDataAdapter(cautruyvan, sqlCn);
           DataTable tb = new DataTable("khachhang");
           try
           {
               da.Fill(tb);
           }
           catch (Exception ex)
           {
               error = ex.Message;
               return null;
           }

           if (tb.Rows.Count > 0)
           {
               DataRow dr = tb.Rows[0];

               tbkhachhangDTO kh = new tbkhachhangDTO();
               kh.Makh = int.Parse(dr["makh"].ToString());
               kh.Tenkh = dr["tenkh"].ToString();
               kh.Diachi = dr["diachi"].ToString();
               kh.Dienthoai = dr["dienthoai"].ToString();
               kh.Email = dr["email"].ToString();
               kh.Matkhau = dr["matkhau"].ToString();
              kh.Diemtichluy = int.Parse(dr["diemtichluy"].ToString());
               return kh;
           }
           return null;
       }
    }
    
}
